package tools;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

public class Importing  
{
	
	static Connection conn;
	public static void main(String[] args) throws Exception
	{
		if (args.length > 0)
		{
			for (String s: args)
			{
				getPlatformSamples(s);
				
				//recordPlatformSamples(getPlatformDatasets("GPL339"), "GPL339");
				//recordPlatformActivations(getPlatformSamples("GPL339"), "GPL339");
			}
		}
		
		
		//sqlRecordGeneMap(); 
		//DataTools.CreatePlatform("GPL32", "Affimetrix");		
		//recordPlatformSamples(getPlatformDatasets("GPL340"), "GPL340");
		
		//recordPlatformActivations(getPlatformSamples("GPL340"), "GPL340");
		
		//recordPlatformSamples(getPlatformDatasets("GPL339"), "GPL339");
		//recordPlatformActivations(getPlatformSamples("GPL339"), "GPL339");
		
		//recordPlatformSamples(getPlatformDatasets("GPL1261"), "GPL1261");
		//recordPlatformActivations(getPlatformSamples("GPL1261"), "GPL1261");
		
		//getPlatformSamples("GPL340");
		
		//getPlatformDatasets("GPL1261");
		//getPlatformSamples("GPL339");
		/*
		clearTempTable("temp_activations");			
		uploadSample("GSM22669", "GPL340");			
		updateSampleID("GSM22669");
		updateExperimentID("GSM22669");
		updateGeneID("GSM22669");
		insertData("GSM22669");*/
		
	}
	
	public static void sqlRecordGeneMap() throws Exception 
	{
		ResultSet rs;
		Connection conn = DataTools.getConnection();
		
		String simpleProc = "{ call recordgene }";
	    
	    CallableStatement cs = conn.prepareCall(simpleProc);	    
	    //cs.registerOutParameter(1, java.sql.Types.INTEGER);	   
	    
	    rs = cs.executeQuery();
	    
	    conn.close();
	    String id, name, pub;
	    while (rs.next()) 
	    {	        
	    	id = rs.getString("PlatformID");
	    	name = rs.getString("PlatformName");
	    	pub = rs.getString("Publisher");
	    	
	    	System.out.println(id + ", " + name + "," + pub);	        
	    }	    
	}
	
	public static ArrayList<String> getSamples() throws Exception
	{
		ResultSet rs;
		ArrayList<String> result = new ArrayList<String>();
		Connection conn = DataTools.getConnection();
		
		String simpleProc = " select GeoCode from samples";
	    
	    CallableStatement cs = conn.prepareCall(simpleProc);   
	    rs = cs.executeQuery();
	    //String name;
	    while (rs.next()) 
	    {	        
	    	result.add(rs.getString("GeoCode"));
	    	//name = rs.getString("GeoCode");    	
	    	
	    	//System.out.println("Geocode: "+ name );	        
	    }
	    conn.close();
	    return result;	
	}
	
	public static ArrayList<String> getPlatformSamples(String platformGeocode) throws Exception
	{
		System.out.println("Get Samples");	
		ResultSet rs;
		ArrayList<String> result = new ArrayList<String>();
		Connection conn = DataTools.getConnection();
		
		String simpleProc = " select distinct samples.geocode " +
					"from samples inner join experiments  " +
					"on samples.experimentid = experiments.experimentid inner join platforms  " +
					"on experiments.platformid = platforms.platformid  " +
					"where platforms.PlatformName = '" +platformGeocode+ "' order by sampleid";
	    
	    CallableStatement cs = conn.prepareCall(simpleProc);   
	    rs = cs.executeQuery();
	    String name;
	    while (rs.next()) 
	    {	
	    	name = rs.getString("GeoCode");    	
	    	
	    	System.out.println("Geocode: "+ name);	
	    	result.add(rs.getString("GeoCode"));
	    }
	    conn.close();
	    return result;	
	}
	
	
	public static void recordPlatformActivations(ArrayList<String> geoNames, String platformGeoCode) throws Exception
	{
		Iterator<String> it = geoNames.iterator();
		
		System.out.println("Update Process start");
		
		while (it.hasNext())
		{
			
			String current = it.next();
			
			System.out.print(current);
			
			clearTempTable("temp_activations");			
			uploadSample(current, platformGeoCode);			
			updateSampleID(current);
			updateExperimentID(current);
			updateGeneID(current);
			insertData(current, platformGeoCode);
			
			
		}
		
		System.out.println("Update Process end");
	}
	
	
	
	public static void uploadSample(String sampleGeoCode, String platformGeoCode) 
	{
		Connection conn = DataTools.getConnection();		
		try 
		{
			//System.out.print(sampleGeoCode + " upload start");
			String  loadSql; 	
			
			loadSql = " LOAD DATA LOCAL INFILE 'C:\\\\Java\\\\microarray\\\\microarrays4\\\\" 
						+platformGeoCode+ "\\\\" +platformGeoCode+ "\\\\" + sampleGeoCode + "_map.txt' " + 
						"INTO TABLE temp_activations " +
						"FIELDS TERMINATED BY '\\t' " +
						"LINES TERMINATED BY '\\n' " +
						"(genename, activation)" ;
			
			CallableStatement cs = conn.prepareCall(loadSql);	
			cs.execute();
			
			//System.out.println(loadSql);
		
			conn.close();
			//System.out.println(sampleGeoCode + " uploaded");
		}
		catch (SQLException se)
		{
			System.out.println(sampleGeoCode + " failed");
		}
	}
	
	public static void updateSampleName(String sampleGeoCode) 
	{
		Connection conn = DataTools.getConnection();		
		try 
		{
			String simpleProc = "update temp_activations, samples " +
						"set temp_activations.sampleid = samples.sampleid "+ 
						"where temp_activations.sampleid is null AND samples.geocode = '" +sampleGeoCode+ "'; " + 
						"update temp_activations, experiments, samples " +
						"set temp_activations.experimentid = experiments.experimentid " +
						"where temp_activations.experimentid is null " +
						"AND experiments.experimentid = samples.experimentid " +
						"AND samples.geocode = '" +sampleGeoCode+ "'" ;			

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			conn.close();
			
			//System.out.println(sampleGeoCode + " updated");
		}
		catch (SQLException se)
		{
			System.out.println(sampleGeoCode + " update failed: " + se.getMessage());
		}
	}
	
	public static void updateExperimentID(String sampleGeoCode) 
	{
		Connection conn = DataTools.getConnection();		
		try 
		{
			String simpleProc = "update temp_activations, experiments, samples " +
						"set temp_activations.experimentid = experiments.experimentid " +
						"where temp_activations.experimentid is null " +
						"AND experiments.experimentid = samples.experimentid " +
						"AND samples.geocode = '" +sampleGeoCode+ "'" ;			

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			conn.close();
			
			System.out.print(" experiment, ");
		}
		catch (SQLException se)
		{
			System.out.println(sampleGeoCode + " experimentid update failed: " + se.getMessage());
		}
	}
	
	public static void updateSampleID(String sampleGeoCode) 
	{
		Connection conn = DataTools.getConnection();		
		try 
		{
			String simpleProc = "update temp_activations, samples " +
						"set temp_activations.sampleid = samples.sampleid "+ 
						"where temp_activations.sampleid is null AND samples.geocode = '" +sampleGeoCode+ "'; ";							

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			//conn.close();
			
			System.out.print(" sample,");
		}
		catch (SQLException se)
		{
			System.out.println(sampleGeoCode + " sampleid update failed: " + se.getMessage());
		}
	}
	
	public static void updateGeneID(String sampleGeoCode) 
	{
		Connection conn = DataTools.getConnection();		
		try 
		{
			String simpleProc = "update temp_activations, platformgenes " +  
						"set temp_activations.geneid = platformgenes.geneid   " +
						"where temp_activations.genename = platformgenes.platformgenename";							

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			conn.close();
			
			System.out.print(" gene, ");
		}
		catch (SQLException se)
		{
			System.out.println(sampleGeoCode + " geneid update failed: " + se.getMessage());
		}
	}
	
	public static void clearTempTable(String tableName) 
	{
		Connection conn = DataTools.getConnection();
		
		try 
		{
			String simpleProc = "truncate table " + tableName;			

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			conn.close();
			
			//System.out.println(tableName + ": truncated");
		}
		catch (SQLException se)
		{
			System.out.println(tableName + " truncate failed: " + se.getMessage());
		}
	}
	
	public static void insertData(String geoName, String platformGeoCode) 
	{
		Connection conn = DataTools.getConnection();
		
		try 
		{
			String simpleProc, tblName; 
			/*String simpleProc = "insert into geneactivations(sampleid, geneid, activation, experimentid) " +
							"select temp_activations.sampleid, " +
							"PlatformGenes.geneid, " +
							"temp_activations.activation, " +
							"temp_activations.experimentid " +
							"from temp_activations inner join PlatformGenes ON " +
							"PlatformGenes.PlatformGeneName = temp_activations.genename";		*/
			
			if ("GPL340".equals(platformGeoCode))
				tblName = "geneactivations2";
			else if ("GPL339".equals(platformGeoCode))
				tblName = "geneactivations3";
			else if ("GPL1261".equals(platformGeoCode))
				tblName = "geneactivations4";
			else
				tblName = "geneactivations";
			
			simpleProc = "insert into " +tblName+ "(sampleid, geneid, activation, experimentid) " +
					"select distinct temp_activations.sampleid, " +
					"temp_activations.geneid, " +
					"temp_activations.activation, " +
					"temp_activations.experimentid " +
					"from temp_activations";

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			conn.close();
			
			System.out.println(" inserted");
		}
		catch (SQLException se)
		{
			System.out.println(geoName + " insert failed: " + se.getMessage());
		}
	}
	
	
	public static void recordPlatformSamples(ArrayList<String> datasets, String platformgeocode)
	{
		Iterator<String> it = datasets.iterator();
		
		System.out.println("Update Process start");
		
		while (it.hasNext())
		{
			String current = it.next();
			clearTempTable("temp_samples");
			uploadDatasetSamples(current, platformgeocode);			
			updateExperimentName(current);			
			insertDatasetSamples(current);			
		}
		
		System.out.println("Update Process end");
	}
	
	public static void uploadDatasetSamples(String datasetGeoName, String platformGeoCode)
	{
		Connection conn = DataTools.getConnection();		
		try 
		{
			//System.out.println(datasetGeoName + " upload start");
			String  loadSql; 
			
			loadSql = " LOAD DATA LOCAL INFILE 'C:\\\\Java\\\\microarray\\\\microarrays4\\\\" 
						+platformGeoCode+ "\\\\" +platformGeoCode+ "\\\\" + datasetGeoName + ".txt' " + 
						"INTO TABLE temp_samples " +
						"FIELDS TERMINATED BY '\\t' " +
						"LINES TERMINATED BY '\\n' " +
						"(sample, desc1, desc2, iscontrol)" ;
			
			CallableStatement cs = conn.prepareCall(loadSql);	
			cs.execute();
			
			System.out.println(loadSql);
		
			conn.close();
			//System.out.println(datasetGeoName + " uploaded");
		}
		catch (SQLException se)
		{
			System.out.println(datasetGeoName + " failed " + se.getMessage());
		}
		
	}
	
	public static ArrayList<String> getPlatformDatasets(String platformGeocode)
	{
		
		ArrayList<String> result = new ArrayList<String>();
		try
		{
			Connection conn = DataTools.getConnection();
			ResultSet rs;
			String simpleProc = " select GeoCode from experiments inner join platforms " +
					"on platforms.platformid = experiments.platformid " +
					"where platforms.platformname = '" +platformGeocode +"'";
		    
		    CallableStatement cs = conn.prepareCall(simpleProc);   
		    rs = cs.executeQuery();
		    String name;
		    while (rs.next()) 
		    {	
		    	name = rs.getString("GeoCode").trim();    	
		    	
		    	System.out.println("Dataset: "+ name);
		    	result.add(name);
		    }
		    conn.close();
		}
		catch (SQLException se)
		{
			System.out.println(platformGeocode + " failed " +se.getMessage());
		}
	    
	    return result;	
	}
	
	public static void updateExperimentName(String datasetGeoName)
	{
		Connection conn = DataTools.getConnection();		
		try 
		{
			String simpleProc = "Update temp_samples, experiments " + 
								"SET temp_samples.experimentid = experiments.experimentid "+
								"WHERE temp_samples.experimentid is null and experiments.geocode = '" +datasetGeoName + "'";			

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			conn.close();
			
			//System.out.println(datasetGeoName + " updated");
		}
		catch (SQLException se)
		{
			System.out.println(datasetGeoName + " update failed: " + se.getMessage());
		}
	}
	
	public static void insertDatasetSamples(String datasetGeoName)
	{
		Connection conn = DataTools.getConnection();
		
		try 
		{
			String simpleProc = "insert into samples (OrganismID, GeoCode, ExperimentID, Description) " +
						"Select DISTINCT 1 As OrganismID,  " +
						"temp_samples.sample, " +
						"temp_samples.experimentid, " +
						"concat(temp_samples.desc1, ';', temp_samples.desc2)" +
						"FROM temp_samples";		

			CallableStatement cs = conn.prepareCall(simpleProc);	
			cs.execute();
			
			conn.close();
			
			//System.out.println(datasetGeoName + ": inserted");
		}
		catch (SQLException se)
		{
			System.out.println(datasetGeoName + " insert failed: " + se.getMessage());
		}
		
	}
	
}
